/*##########Comment by owner: ##########*/
if exists (select 1
		   from master.dbo.sysdatabases
		   where name = 'Dosvid')
  drop database Dosvid
go

create database Dosvid
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'ViewVariants')
  drop table ViewVariants
go

create table ViewVariants
(
  Id int identity(1,1) not null,
  ViewVariantName nvarchar(100) not null
)  
go

alter table ViewVariants add constraint PK_ViewVariants_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Roles')
  drop table Roles
go

create table Roles
(
  Id int identity(1,1) not null,
  RoleName nvarchar(100) not null,
  Positions_Edit bit not null,
  Positions_ViewId int not null,
  Territories_Edit bit not null, 
  Territories_ViewId int not null, 
  CommodityGroups_Edit bit not null,
  CommodityGroups_ViewId int not null,
  Users_Edit bit not null,
  Users_ViewId int not null,
  Employers_Edit bit not null,
  Employers_ViewId int not null,
  Vacancies_Edit bit not null,
  Vacancies_ViewId int not null,
  Candidates_Edit bit not null,
  Candidates_ViewId int not null
)  
go

alter table Roles add constraint PK_Roles_ID primary key(Id)
go

---

alter table Roles add constraint FK_Roles_ViewVariants_Positions 
      foreign key (Positions_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Positions_Edit
      default (0) for Positions_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Territories 
      foreign key (Territories_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Territories_Edit
      default (0) for Territories_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_CommodityGroups 
      foreign key (CommodityGroups_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_CommodityGroups_Edit
      default (0) for CommodityGroups_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Users 
      foreign key (Users_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Users_Edit
      default (0) for Users_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Employers 
      foreign key (Employers_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Employers_Users_Edit
      default (0) for Employers_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Vacancies 
      foreign key (Vacancies_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Vacancies_Edit
      default (0) for Vacancies_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Candidates 
      foreign key (Candidates_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Candidates_Edit
      default (0) for Candidates_Edit
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Ages')
  drop table Ages
go

create table Ages
(
  Id int identity(1,1) not null,
  AgeName int not null
)  
go

alter table Ages add constraint PK_Ages_ID primary key(Id)
go


 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'CompareImportants')
  drop table CompareImportants
go

create table CompareImportants
(
  Id int identity(1,1) not null,
  CompareImportantName nvarchar(100) not null,
  CompareImportantLevel smallint not null
)  
go

alter table CompareImportants add constraint PK_CompareImportants_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'HasCar')
  drop table HasCar
go

create table HasCar
(
  Id int identity(1,1) not null,
  HasCarName nvarchar(100) not null
)  
go

alter table HasCar add constraint PK_HasCar_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'SexVariants')
  drop table SexVariants
go

create table SexVariants
(
  Id int identity(1,1) not null,
  SexName nvarchar(100) not null,
  UseInVacancy bit not null
)  
go

alter table SexVariants add constraint PK_SexVariants_ID primary key(Id)
go


alter table SexVariants with check add constraint DF_SexVariants_UseInVacancy
      default (0) for UseInVacancy
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Positions')
  drop table Positions
go

create table Positions
(
  Id int identity(1,1) not null,
  PositionName nvarchar(100) not null
)  
go

alter table Positions add constraint PK_Positions_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Territories')
  drop table Territories
go

create table Territories
(
  Id int identity(1,1) not null,
  TerritoryName nvarchar(100) not null
)  
go

alter table Territories add constraint PK_Territories_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'SalaryRanges')
  drop table SalaryRanges
go

create table SalaryRanges
(
  Id int identity(1,1) not null,
  SalaryRange int not null
)  
go

alter table SalaryRanges add constraint PK_SalaryRanges_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'CommodityGroups')
  drop table CommodityGroups
go


create table CommodityGroups
(
  Id int identity(1,1) not null,
  CommodityGroupName nvarchar(100) not null
)  
go

alter table CommodityGroups add constraint PK_CommodityGroups_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'InterviewResults')
  drop table InterviewResults
go

create table InterviewResults
(
  Id int identity(1,1) not null,
  InterviewResultName nvarchar(100) not null
)  
go

alter table InterviewResults add constraint PK_InterviewResults_ID primary key(Id)
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Users')
  drop table Users
go

create table Users
(
  Id int identity(1,1) not null,
  UserName nvarchar(50) not null,
  [Password] nvarchar(20) not null,
  RoleID int not null,
  Active bit not null,
  EMail nvarchar(100) null,
  Emil_UserName nvarchar(50) null,
  EMail_Password nvarchar(20) null,
  EMail_SMTP_Server nvarchar(100) null
)  
go

alter table Users add constraint PK_Users_ID primary key(Id)
go

alter table Users add constraint FK_Users_Roles_RoleID 
      foreign key (RoleID) references Roles(Id) on delete cascade
go

alter table Users with check add constraint DF_Users_Active
      default (1) for Active
go

alter table Users with check add constraint Password_Not_Empty
      check (Len(IsNull([Password],'')) > 0)
go

create unique nonclustered index IX_Users_UserName on Users
       (UserName asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidates')
  drop table Candidates
go

create table Candidates
(
  Id int identity(1,1) not null,
  EnterDate datetime not null,
  DocumnetNumber nvarchar(255) null,
  DocumentSum numeric(19,2) null,
  LastName nvarchar(255) not null,
  DateOfBirthDay datetime null,
  Adress nvarchar(255) null,
  ApproveOnPost bit not null, 
  PrePaid nvarchar(255) null,
  ManagerId int not null,
  [Resume] image null,
  [ResumeName] nvarchar(255) null,
  SexID int not null,
  CarID int null,
  Education nvarchar(255) null,
  EducationType nvarchar(255) null,
  Characteristic text null,
  TerritoryID int null,
  SalaryRangeID int null,
  PositionID int null,
  Position_CompareImportantID int null,
  Territory_CompareImportantID int null,
  SalaryRange_CompareImportantID int null,
  OtherReqirments text null,
  OnlyTradeMarks nvarchar(255),
  RangeTerritoryID int null
)  
go

alter table Candidates add constraint PK_Candidates_ID primary key(Id)
go


alter table Candidates with check add constraint DF_Candidates_ApproveOnPost
      default (0) for ApproveOnPost
go


alter table Candidates add constraint FK_Candidates_ManagerId 
      foreign key (ManagerId) references Users(Id) on delete cascade
go


alter table Candidates add constraint FK_Candidates_Positions 
      foreign key (PositionID) references Positions(Id) 
go

create nonclustered index IX_Candidates_ManagerId on Candidates
       (ManagerId asc)
go


alter table Candidates add constraint FK_Candidates_SalaryRanges_SalaryRangeID 
      foreign key (SalaryRangeID) references SalaryRanges(Id) 
go


alter table Candidates add constraint FK_Candidates_Territories_TerritoryId 
      foreign key (TerritoryId) references Territories(Id) 
go


alter table Candidates add constraint FK_Candidates_Territories_RangeTerritoryId 
      foreign key (RangeTerritoryId) references Territories(Id) 
go

alter table Candidates add constraint FK_Candidates_HasCar_CarID 
      foreign key (CarID) references HasCar(Id) 
go


alter table Candidates add constraint FK_Candidates_SexVariants_SexID 
      foreign key (SexID) references SexVariants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_Position_CompareImportantID 
      foreign key (Position_CompareImportantID) references CompareImportants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_Territory_CompareImportantID 
      foreign key (Territory_CompareImportantID) references CompareImportants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_SalaryRange_CompareImportantID 
      foreign key (SalaryRange_CompareImportantID) references CompareImportants(Id) 
go


--alter table Candidates with check add constraint CK_Candidates_Position_Choose
--check ((PositionID is null and Position_CompareImportantID is null) or
--       (PositionID is not null and Position_CompareImportantID is not null))
--go


--alter table Candidates with check add constraint CK_Candidates_SalaryRange_Choose
--check ((SalaryRangeID is null and SalaryRange_CompareImportantID is null) or
--       (SalaryRangeID is not null and SalaryRange_CompareImportantID is not null))
--go

--alter table Candidates with check add constraint CK_Candidates_Territory_Choose
--check ((TerritoryID is null and Territory_CompareImportantID is null) or
--       (TerritoryID is not null and Territory_CompareImportantID is not null))
--go


 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Employers')
  drop table Employers
go

create table Employers
(
  Id int identity(1,1) not null,
  EmployerName nvarchar(255) not null,
  [OwnerShip] nvarchar(255) null,
  AddressInLviv nvarchar(255) null,
  AddressOfMainOffice nvarchar(255) null,
  DirectionOfBusiness nvarchar(255) null,
  TradeMarks nvarchar(255) null,
  WebResource nvarchar(255) null,
  DateOfFirstMeet datetime null,
  ManagerId int not null
)  
go

alter table Employers add constraint PK_Employers_ID primary key(Id) 
go


alter table Employers add constraint FK_Employers_ManagerId 
      foreign key (ManagerId) references Users(Id) on delete cascade
go


create nonclustered index IX_Employers_ManagerId on Employers
       (ManagerId asc)
go


 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Employer_CommodityGroups')
  drop table Employer_CommodityGroups
go

create table Employer_CommodityGroups
(
  Id int identity(1,1) not null,
  EmployerId int not null,
  CommodityGroupId int not null
)  
go

alter table Employer_CommodityGroups add constraint PK_Employer_CommodityGroups_ID primary key(Id) 
go


alter table Employer_CommodityGroups add constraint FK_Employer_CommodityGroups_CommodityGroups_CommodityGroupId 
      foreign key (CommodityGroupId) references CommodityGroups(Id) on delete cascade
go


alter table Employer_CommodityGroups add constraint FK_Employer_CommodityGroups_Employers_EmployerId 
      foreign key (EmployerId) references Employers(Id) on delete cascade
go


create nonclustered index IX_Employer_CommodityGroups_EmployerId on Employer_CommodityGroups
       (EmployerId asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Employer_ContactPersons')
  drop table Employer_ContactPersons
go

create table Employer_ContactPersons
(
  Id int identity(1,1) not null,
  EmployerId int not null,
  CandidatID int not null
)  
go

alter table Employer_ContactPersons add constraint PK_Employer_ContactPersons_ID primary key(Id) 
go


alter table Employer_ContactPersons add constraint FK_Employer_ContactPersons_Employers_EmployerID 
      foreign key (EmployerId) references Employers(Id) on delete cascade
go


alter table Employer_ContactPersons add constraint FK_Employer_ContactPersons_Candidates_CandidatId 
      foreign key (CandidatID) references Candidates(Id) 
go


create nonclustered index IX_Employer_ContactPersons_EmployerId on Employer_ContactPersons
       (EmployerId asc)
go


create nonclustered index IX_Employer_ContactPersons_CandidatId on Employer_ContactPersons
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_EMails')
  drop table Candidat_EMails
go

create table Candidat_EMails
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  EMail nvarchar(255) not null
)  
go

alter table Candidat_EMails add constraint PK_Candidat_EMails_ID primary key(Id) 
go


alter table Candidat_EMails add constraint FK_Candidat_EMails_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go


create nonclustered index IX_Candidat_EMails_CandidatId on Candidat_EMails
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_ExceptCommodityGroups')
  drop table Candidat_ExceptCommodityGroups
go

create table Candidat_ExceptCommodityGroups
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  CommodityGroupId int not null
)  
go

alter table Candidat_ExceptCommodityGroups add constraint PK_Candidat_ExceptCommodityGroups_ID primary key(Id) 
go


alter table Candidat_ExceptCommodityGroups add constraint FK_Candidat_ExceptCommodityGroups_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go


alter table Candidat_ExceptCommodityGroups add constraint FK_Candidat_ExceptCommodityGroups_CommodityGroups_CommodityGroupId 
      foreign key (CommodityGroupId) references CommodityGroups(Id) on delete cascade
go


create nonclustered index IX_Candidat_ExceptCommodityGroups_CandidatId on Candidat_ExceptCommodityGroups
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_ExceptEmployers')
  drop table Candidat_ExceptEmployers
go

create table Candidat_ExceptEmployers
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  EmployerID int not null
)  
go

alter table Candidat_ExceptEmployers add constraint PK_Candidat_ExceptEmployers_ID primary key(Id) 
go


alter table Candidat_ExceptEmployers add constraint FK_Candidat_ExceptEmployers_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go


alter table Candidat_ExceptEmployers add constraint FK_Candidat_ExceptEmployers_Employers_EmployerID 
      foreign key (EmployerId) references Employers(Id) 
go


create nonclustered index IX_Candidat_ExceptEmployer_CandidatId on Candidat_ExceptEmployers
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_Expiriences')
  drop table Candidat_Expiriences
go

create table Candidat_Expiriences
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  PositionID int not null,
  Years numeric(3,1) not null  
)  
go

alter table Candidat_Expiriences add constraint PK_Candidat_Expiriences_ID primary key(Id) 
go


alter table Candidat_Expiriences add constraint FK_Candidat_Expiriences_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go


alter table Candidat_Expiriences add constraint FK_Candidat_Expiriences_Positions_CandidatID 
      foreign key (PositionID) references Positions(Id) on delete cascade
go


create nonclustered index IX_Candidat_Expiriences_CandidatId on Candidat_Expiriences
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_Phones')
  drop table Candidat_Phones
go

create table Candidat_Phones
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  Phone nvarchar(13) not null
)  
go

alter table Candidat_Phones add constraint PK_Candidat_Phones_ID primary key(Id) 
go


alter table Candidat_Phones add constraint FK_Candidat_Phones_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go


create nonclustered index IX_Candidat_Phones_CandidatId on Candidat_Phones
       (CandidatID asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Vacancies')
  drop table Vacancies
go

create table Vacancies
(
  Id int identity(1,1) not null,
  VacancyDate datetime not null,
  EmployerID int not null,
  PositionID int not null,
  EmployeeWhoFindCandidatesID int null,
  EmployeeWhoApproveCandidatesID int null,
  TradeMarks nvarchar(255) null,
  SalaryRangeID int null,
  SalaryDate datetime null,
  TrialPeriod nvarchar(255),
  SalaryOnTrialPeriod nvarchar(255),
  TerritoryId int null,
  Comment text,
  ExpirienceYear numeric(4,2) null,
  AgeMin int null,
  AgeMax int null,
  CarID int null,
  Education nvarchar(255) null,
  EducationType nvarchar(255) null,
  SexID int null,
  Position_CompareImportantID int null,
  ExpirienceYear_CompareImportantID int null,
  Age_CompareImportantID int null,
  Car_CompareImportantID int null,
  Sex_CompareImportantID int null
)  
go

alter table Vacancies add constraint PK_Vacancies_ID primary key(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Candidates_EmployeeWhoFindCandidatesID 
      foreign key (EmployeeWhoFindCandidatesID) references Candidates(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Candidates_EmployeeWhoApproveCandidatesID 
      foreign key (EmployeeWhoApproveCandidatesID) references Candidates(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Employers_EmployerID 
      foreign key (EmployerID) references Employers(Id) on delete cascade
go


alter table Vacancies add constraint FK_Vacancies_Employers_PositionID 
      foreign key (PositionID) references Positions(Id) on delete cascade
go


alter table Vacancies add constraint FK_Vacancies_SalaryRanges_SalaryRangeID 
      foreign key (SalaryRangeID) references SalaryRanges(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Territories_TerritoryId 
      foreign key (TerritoryId) references Territories(Id) 
go

alter table Vacancies add constraint FK_Vacancies_HasCar_CarID 
      foreign key (CarID) references HasCar(Id) 
go


alter table Vacancies add constraint FK_Vacancies_SexVariants_SexID 
      foreign key (SexID) references SexVariants(Id) 
go

create nonclustered index IX_Employers_EmployerID on Vacancies
       (EmployerID asc)
go


alter table Vacancies add constraint FK_Vacancies_CompareImportants_Position_CompareImportantID 
      foreign key (Position_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_ExpirienceYear_CompareImportantID 
      foreign key (ExpirienceYear_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Sex_CompareImportantID 
      foreign key (Sex_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Car_CompareImportantID 
      foreign key (Car_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Age_CompareImportantID 
      foreign key (Age_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_Ages_AgeMin 
      foreign key (AgeMin) references Ages(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Ages_AgeMax 
      foreign key (AgeMax) references Ages(Id) 
go

/*
alter table Vacancies with check add constraint CK_Vacancies_ExpirienceYear_CompareImportantID_Choose
check ((ExpirienceYear is null and ExpirienceYear_CompareImportantID is null) or
       (ExpirienceYear is not null and ExpirienceYear_CompareImportantID is not null))
go


alter table Vacancies with check add constraint CK_Vacancies_Sex_CompareImportantID_Choose
check ((SexID is null and Sex_CompareImportantID is null) or
       (SexID is not null and Sex_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Car_CompareImportantID_Choose
check ((CarID is null and Car_CompareImportantID is null) or
       (CarID is not null and Car_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Age_CompareImportantID_Choose
check ((AgeMin is null and AgeMax is null and Age_CompareImportantID is null) or
       (AgeMin is not null and AgeMax is not null and Age_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Mandatory_choise
check (not(EmployeeWhoFindCandidatesID is null and
	       EmployeeWhoApproveCandidatesID is null))
go
*/
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Vacancy_CommodityGroups')
  drop table Vacancy_CommodityGroups
go

create table Vacancy_CommodityGroups
(
  Id int identity(1,1) not null,
  VacancyId int not null,
  CommodityGroupId int not null
)  
go

alter table Vacancy_CommodityGroups add constraint PK_Vacancy_CommodityGroups_ID primary key(Id) 
go


alter table Vacancy_CommodityGroups add constraint FK_Vacancy_CommodityGroups_CommodityGroups_CommodityGroupId 
      foreign key (CommodityGroupId) references CommodityGroups(Id) on delete cascade
go


alter table Vacancy_CommodityGroups add constraint FK_Vacancy_CommodityGroups_CommodityGroups_VacancyId 
      foreign key (VacancyId) references Vacancies(Id) on delete cascade
go


create nonclustered index IX_Vacancy_CommodityGroups_VacancyId on Vacancy_CommodityGroups
       (VacancyId asc)
go

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_VacancyHistory')
  drop table Candidat_VacancyHistory
go

create table Candidat_VacancyHistory
(
  Id int identity(1,1) not null,
  CandidatId int not null,
  VacancyId int not null,
  SendResumeDate datetime null,
  InterviewDate datetime null,
  InterviewResultId int null,
  ResultDate datetime null,
  IsAutoAdded bit not null 
)  
go

alter table Candidat_VacancyHistory add constraint PK_Candidat_VacancyHistory_ID primary key(Id)
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_Candidates_CandidatId 
      foreign key (CandidatId) references Candidates(Id) on delete cascade
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_Vacancies_VacancyId 
      foreign key (VacancyId) references Vacancies(Id) 
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_InterviewResults_InterviewResultID 
      foreign key (InterviewResultId) references InterviewResults(Id) on delete cascade
go


create nonclustered index IX_Candidat_VacancyHistory_CandidatId on Candidat_VacancyHistory
       (CandidatId asc)
go

create nonclustered index IX_Candidat_VacancyHistory_VacancyId on Candidat_VacancyHistory
       (VacancyID asc)
go


alter table Candidat_VacancyHistory with check add constraint DF_Candidat_VacancyHistory_IsAutoAdded
      default (0) for IsAutoAdded
go
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

-------ViewVariants-------------------------------

-------ViewVariants-------------------------------

insert into ViewVariants(ViewVariantName)
Values ('All')
go

--Where I`m owner of entity
insert into ViewVariants(ViewVariantName)
Values ('Owner')
go

insert into ViewVariants(ViewVariantName)
Values ('Nothing')
go

--------Roles-------------------------------------
insert into Roles(RoleName,Positions_Edit,Positions_ViewId,Territories_Edit, 
				  Territories_ViewId, CommodityGroups_Edit, CommodityGroups_ViewId,
				  Users_Edit, Users_ViewId, Employers_Edit, Employers_ViewId,
				  Vacancies_Edit, Vacancies_ViewId, Candidates_Edit, Candidates_ViewId)
values('Администратор',1,1,1,1,1,1,1,1,1,1,1,1,1,1)
go

insert into Roles(RoleName,Positions_Edit,Positions_ViewId,Territories_Edit, 
				  Territories_ViewId, CommodityGroups_Edit, CommodityGroups_ViewId,
				  Users_Edit, Users_ViewId, Employers_Edit, Employers_ViewId,
				  Vacancies_Edit, Vacancies_ViewId, Candidates_Edit, Candidates_ViewId)
values('Менеджер Кандидатов',0,1,0,1,0,1,0,1,0,3,0,3,1,2)
go

insert into Roles(RoleName,Positions_Edit,Positions_ViewId,Territories_Edit, 
				  Territories_ViewId, CommodityGroups_Edit, CommodityGroups_ViewId,
				  Users_Edit, Users_ViewId, Employers_Edit, Employers_ViewId,
				  Vacancies_Edit, Vacancies_ViewId, Candidates_Edit, Candidates_ViewId)
values('Менеджер Вакансий',0,1,0,1,0,1,0,1,0,2,1,2,0,1)
go

-------Ages------------------------------------------

declare @i int
set @i = 16

while @i <= 70 begin
 insert into Ages (AgeName)
 Values(@i)
 
 set @i = @i + 1
end
go

------HasCar----------------------------------------
insert into HasCar(HasCarName)Values('Да')
go
insert into HasCar(HasCarName)Values('Нет')
go
insert into HasCar(HasCarName)Values('Права')
go

------SexVariants-----------------------------------
insert into SexVariants(SexName,UseInVacancy)Values('Мужской',0)
go
insert into SexVariants(SexName,UseInVacancy)Values('Женский',0)
go
insert into SexVariants(SexName,UseInVacancy)Values('Не важно',1)
go

------CompareImortance-------------------------------
insert into CompareImportants(CompareImportantName,CompareImportantLevel)
Values('Важно',1)
go
insert into CompareImportants(CompareImportantName,CompareImportantLevel)
Values('Желательно',0)
go

-------SalaryRanges----------------------------------
insert into SalaryRanges(SalaryRange)
values(1500)
go
insert into SalaryRanges(SalaryRange)
values(1600)
go
insert into SalaryRanges(SalaryRange)
values(1700)
go
insert into SalaryRanges(SalaryRange)
values(1800)
go
insert into SalaryRanges(SalaryRange)
values(1900)
go
insert into SalaryRanges(SalaryRange)
values(2000)
go
insert into SalaryRanges(SalaryRange)
values(2100)
go
insert into SalaryRanges(SalaryRange)
values(2200)
go
insert into SalaryRanges(SalaryRange)
values(2300)
go
insert into SalaryRanges(SalaryRange)
values(2400)
go
insert into SalaryRanges(SalaryRange)
values(2500)
go
insert into SalaryRanges(SalaryRange)
values(2600)
go
insert into SalaryRanges(SalaryRange)
values(2700)
go
insert into SalaryRanges(SalaryRange)
values(2800)
go
insert into SalaryRanges(SalaryRange)
values(2900)
go
insert into SalaryRanges(SalaryRange)
values(3000)
go
insert into SalaryRanges(SalaryRange)
values(3100)
go
insert into SalaryRanges(SalaryRange)
values(3200)
go
insert into SalaryRanges(SalaryRange)
values(3300)
go
insert into SalaryRanges(SalaryRange)
values(3400)
go
insert into SalaryRanges(SalaryRange)
values(3500)
go
insert into SalaryRanges(SalaryRange)
values(4000)
go
insert into SalaryRanges(SalaryRange)
values(4500)
go
insert into SalaryRanges(SalaryRange)
values(5000)
go
insert into SalaryRanges(SalaryRange)
values(6000)
go
insert into SalaryRanges(SalaryRange)
values(7000)
go
insert into SalaryRanges(SalaryRange)
values(8000)
go
insert into SalaryRanges(SalaryRange)
values(9000)
go
insert into SalaryRanges(SalaryRange)
values(10000)
go
insert into SalaryRanges(SalaryRange)
values(15000)
go
insert into SalaryRanges(SalaryRange)
values(20000)
go
insert into SalaryRanges(SalaryRange)
values(25000)
go

---------InterviewResults-------------------------------------------
insert into InterviewResults(InterviewResultName)
Values('Принят')
go
insert into InterviewResults(InterviewResultName)
Values('Отклонен')
go
insert into InterviewResults(InterviewResultName)
Values('Думают')
go
insert into InterviewResults(InterviewResultName)
Values('Не пришел на собеседования')
go
insert into InterviewResults(InterviewResultName)
Values('Ожидается повторное собеседования')
go
insert into InterviewResults(InterviewResultName)
Values('Повторное собеседования')
go


----Users-----------------------

insert into [Users]([UserName],[Password],[RoleID],[Active])
values('Admin','1',1,1)
 
 go
 
 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

IF NOT EXISTS(SELECT name
			  FROM master.dbo.syslogins 
			  WHERE name = 'WorkUser')
BEGIN
    CREATE LOGIN WorkUser WITH PASSWORD = 'DosvidAndKolj'
END
go

if not exists(SELECT 1
			  FROM [dbo].[sysusers]
			  where [name] = 'WorkUser')
begin
 CREATE USER WorkUser FOR LOGIN WorkUser
 EXEC sp_addrolemember N'db_owner', N'WorkUser'
end


 
 

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'sp_GetVacanciesForCandidate')
  drop procedure sp_GetVacanciesForCandidate
go

create PROCEDURE sp_GetVacanciesForCandidate(@CandidateID int) 
AS
BEGIN
  if object_id('TempDB..#SelectedMandatory') is not null 
    drop table #SelectedMandatory
    
  create table #SelectedMandatory(VacancyID int, Value int)
 
  --insert record by mandatory conditions
  insert into #SelectedMandatory(VacancyID,Value)
  select v.Id, 0  
  from Vacancies v
  join Employers e on e.Id = v.EmployerID
  join Candidates c on c.Id = @CandidateID
  where (((v.Car_CompareImportantID = 1) and (v.CarID = c.CarID)) or
        (isnull(v.Car_CompareImportantID,2) = 2)) 
        and
        (((v.Age_CompareImportantID = 1) and 
          (DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between 
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMin),1) and
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMax),100))) or
         (isnull(v.Age_CompareImportantID,2) = 2))
        and
        (((v.Sex_CompareImportantID = 1) and ((v.SexID = 3) or (v.SexID = c.SexID))) or
         (isnull(v.Sex_CompareImportantID,2) = 2))
        and
        (((v.ExpirienceYear_CompareImportantID = 1) and (v.ExpirienceYear <= (select SUM(ce.Years)	
																			  from Candidat_Expiriences ce
																			  where ce.CandidatID = c.Id and
																				    ce.PositionID = v.PositionID))) or
		 (isnull(v.ExpirienceYear_CompareImportantID,2) = 2))
		and
		((((v.Position_CompareImportantID = 1) or (c.Position_CompareImportantID = 1)) and
		   (v.PositionID = c.PositionID)) or
		 ((isnull(v.Position_CompareImportantID,2) = 2) and (isnull(c.Position_CompareImportantID,2) = 2)))
		and
		(((c.SalaryRange_CompareImportantID = 1) and (c.SalaryRangeID <= v.SalaryRangeID)) or
		 (isnull(c.SalaryRange_CompareImportantID,2) = 2))
		and
		(((c.Territory_CompareImportantID = 1) and (c.RangeTerritoryID = v.TerritoryId)) or
		 (isnull(c.Territory_CompareImportantID,2) = 2))
		and
		e.Id not in (select cee.EmployerID
					 from Candidat_ExceptEmployers cee
					 where cee.CandidatID = c.Id)
		and 
		(
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id and
			         vcg.CommodityGroupId not in (select cecg.CommodityGroupId
												  from Candidat_ExceptCommodityGroups cecg
												  where cecg.CandidatID = c.Id))
		or
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id
			   having COUNT(1) = 0)
	    )
		 
  -- update record value by passed conditions
  update sm set
    sm.Value = case
			     when v.CarID = c.CarID then 1
				 else 0
			   end +
			   case 
			     when DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between v.AgeMin and v.AgeMax
			       then 1
			     else 0
			   end +
			   case 
			     when (v.SexID = 3) or (v.SexID = c.SexID) then 1
			     else 0
			   end +
			   case 
			     when v.ExpirienceYear <= (select SUM(ce.Years)	
										   from Candidat_Expiriences ce
										   where ce.CandidatID = c.Id and
										         ce.PositionID = v.PositionID) then 1
                 else 0
               end +
               case 
                 when v.PositionID = c.PositionID then 1
                 else 0
               end +
               case 
                 when c.SalaryRangeID <= v.SalaryRangeID then 1
                 else 0
               end +
               case
                 when c.RangeTerritoryID = v.TerritoryId then 1
                 else 0
               end
  from #SelectedMandatory sm
  join Vacancies v on v.Id = sm.VacancyID
  join Candidates c on c.Id = @CandidateID
  
  --output
  select v.*
  from Vacancies v
  join #SelectedMandatory sm on v.Id = sm.VacancyID
  order by sm.Value desc
END
GO

 -- Application Delimiter

/*##########Comment by owner: ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'sp_GetCandidatesForVacancies')
  drop procedure sp_GetCandidatesForVacancies
go

create PROCEDURE sp_GetCandidatesForVacancies(@VacancyID int) 
AS
BEGIN
  if object_id('TempDB..#SelectedMandatory') is not null 
    drop table #SelectedMandatory
    
  create table #SelectedMandatory(CandidateID int, Value int)
 
  --insert record by mandatory conditions
  insert into #SelectedMandatory(CandidateID,Value)
  select c.Id, 0  
  from Candidates c
  join Vacancies v on v.Id = @VacancyID
  join Employers e on e.Id = v.EmployerID
  where (((v.Car_CompareImportantID = 1) and (v.CarID = c.CarID)) or
        (isnull(v.Car_CompareImportantID,2) = 2)) 
        and
        (((v.Age_CompareImportantID = 1) and 
          (DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between 
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMin),1) and
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMax),100))) or
         (isnull(v.Age_CompareImportantID,2) = 2))
        and
        (((v.Sex_CompareImportantID = 1) and ((v.SexID = 3) or (v.SexID = c.SexID))) or
         (isnull(v.Sex_CompareImportantID,2) = 2))
        and
        (((v.ExpirienceYear_CompareImportantID = 1) and (v.ExpirienceYear <= (select SUM(ce.Years)	
																			  from Candidat_Expiriences ce
																			  where ce.CandidatID = c.Id and
																				    ce.PositionID = v.PositionID))) or
		 (isnull(v.ExpirienceYear_CompareImportantID,2) = 2))
		and
		((((v.Position_CompareImportantID = 1) or (c.Position_CompareImportantID = 1)) and
		   (v.PositionID = c.PositionID)) or
		 ((isnull(v.Position_CompareImportantID,2) = 2) and (isnull(c.Position_CompareImportantID,2) = 2)))
		and
		(((c.SalaryRange_CompareImportantID = 1) and (c.SalaryRangeID <= v.SalaryRangeID)) or
		 (isnull(c.SalaryRange_CompareImportantID,2) = 2))
		and
		(((c.Territory_CompareImportantID = 1) and (c.RangeTerritoryID = v.TerritoryId)) or
		 (isnull(c.Territory_CompareImportantID,2) = 2))
		and
		e.Id not in (select cee.EmployerID
					 from Candidat_ExceptEmployers cee
					 where cee.CandidatID = c.Id)
		and 
		(
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id and
			         vcg.CommodityGroupId not in (select cecg.CommodityGroupId
												  from Candidat_ExceptCommodityGroups cecg
												  where cecg.CandidatID = c.Id))
		or
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id
			   having COUNT(1) = 0)
	    )
		 
  -- update record value by passed conditions
  update sm set
    sm.Value = case
			     when v.CarID = c.CarID then 1
				 else 0
			   end +
			   case 
			     when DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between v.AgeMin and v.AgeMax
			       then 1
			     else 0
			   end +
			   case 
			     when (v.SexID = 3) or (v.SexID = c.SexID) then 1
			     else 0
			   end +
			   case 
			     when v.ExpirienceYear <= (select SUM(ce.Years)	
										   from Candidat_Expiriences ce
										   where ce.CandidatID = c.Id and
										         ce.PositionID = v.PositionID) then 1
                 else 0
               end +
               case 
                 when v.PositionID = c.PositionID then 1
                 else 0
               end +
               case 
                 when c.SalaryRangeID <= v.SalaryRangeID then 1
                 else 0
               end +
               case
                 when c.RangeTerritoryID = v.TerritoryId then 1
                 else 0
               end
  from #SelectedMandatory sm
  join Candidates c on c.Id = sm.CandidateID
  join Vacancies v on v.Id = @VacancyID
  
  
  --output
  select c.*
  from Candidates c
  join #SelectedMandatory sm on c.Id = sm.CandidateID
  order by sm.Value desc
END
GO

 -- Application Delimiter

